const { pool } = require("../config/database");
const { generateUUID, writeLog } = require("../utils/index");

// Log database operations to file
const logDbQuery = (sql, params) => {
  writeLog("db", `SQL: ${sql}, Params: ${JSON.stringify(params)}`);
};

class BaseModel {
  constructor(tableName, fields) {
    this.tableName = tableName;
    this.fields = fields;

    // 创建数据库连接池 和 生成uuid
    this.pool = pool;
    this.uuid = generateUUID;
  }

  /**
   * 查询列表
   * @param {Object} query - 查询条件对象
   * @returns {Promise<Array<Object>>} 查询结果列表
   */
  async getList(query = {}, ctx) {
    let whereClause = "";
    const params = [];
    const conditions = [`isDelete = 0`];

    // 动态构建 WHERE 子句，只保留有效字段
    for (const [key, value] of Object.entries(query)) {
      if (
        this.fields[key] !== undefined &&
        value !== undefined &&
        value !== null
      ) {
        conditions.push(`${key} = ?`);
        params.push(value);
      }
    }

    if (conditions.length > 0) {
      whereClause = `WHERE ${conditions.join(" AND ")}`;
    }

    const sql = `SELECT * FROM ${this.tableName} ${whereClause}`;
    logDbQuery(sql, params);
    const [rows] = await pool.query(sql, params);
    return rows;
  }

  /**
   * 分页查询记录
   * @param {Object} query - 查询条件对象（必须包含 pageIndex 和 pageSize）
   * @returns {Promise<{list: Array<Object>, total: number}>} 分页数据及总记录数
   * @throws {Error} 如果 pageIndex 或 pageSize 无效
   */
  async getPage(query = {}, ctx) {
    let { pageIndex, pageSize, ...restQuery } = query;

    // 校验分页参数
    try {
      if (typeof pageIndex !== "number") {
        pageIndex = parseInt(pageIndex) || 1;
      }
      if (typeof pageSize !== "number") {
        pageSize = parseInt(pageSize) || 10;
      }
    } finally {
      pageIndex = pageIndex || 1;
      pageSize = pageSize || 10;
    }

    // 计算偏移量
    const offset = (pageIndex - 1) * pageSize;

    // 动态构建 WHERE 子句
    let whereClause = "";
    const params = [];
    const conditions = [`isDelete = 0`];

    for (const [key, value] of Object.entries(restQuery)) {
      if (
        this.fields[key] !== undefined &&
        value !== undefined &&
        value !== null
      ) {
        conditions.push(`${key} = ?`);
        params.push(value);
      }
    }

    if (conditions.length > 0) {
      whereClause = `WHERE ${conditions.join(" AND ")}`;
    }

    // 查询分页数据
    const sql = `SELECT * FROM ${this.tableName} ${whereClause} LIMIT ? OFFSET ?`;
    logDbQuery(sql, [...params, pageSize, offset]);
    const [rows] = await pool.query(sql, [...params, pageSize, offset]);

    // 查询总记录数【待测试】
    const countSql = `SELECT COUNT(*) as total FROM ${this.tableName} ${whereClause}`;
    logDbQuery(countSql, params);
    const [totalResult] = await pool.query(countSql, params);

    return {
      list: rows,
      total: totalResult[0].total,
    };
  }

  /**
   * 添加记录
   * @param {Object} data - 数据对象
   * @returns {Promise<Object>} 新增的记录对象
   */
  async add(data, ctx) {
    // 过滤掉不属于表的字段和 id
    const filteredData = {};
    for (const key in data) {
      if (this.fields[key] !== undefined && key !== "id") {
        filteredData[key] = data[key];
      }
    }

    if (Object.keys(filteredData).length === 0) {
      throw new Error("新增数据字段为空");
    }

    const _user = ctx.state.user || {};
    filteredData.createBy = _user.id;
    filteredData.createDate = new Date();
    filteredData.updateBy = _user.id;
    filteredData.updateDate = new Date();

    let _id = data.id;
    if (!!_id) {
      if (await this.getById(_id)) {
        throw new Error("ID 已存在");
      }
    } else {
      _id = generateUUID();
    }
    filteredData.id = _id;

    const sql = `INSERT INTO ${this.tableName} SET ?`;
    logDbQuery(sql, filteredData);
    const [result] = await pool.query(sql, filteredData);

    if (result.affectedRows === 1) {
      return { ...filteredData };
    } else {
      throw new Error("添加失败");
    }
  }

  /**
   * 更新记录
   * @param {number} id - 记录 ID
   * @param {Object} data - 数据对象
   * @returns {Promise<Object>} 更新后的记录对象
   */
  async update(id, data, ctx) {
    // 过滤掉不属于表的字段
    const filteredData = {};
    for (const key in data) {
      if (this.fields[key] !== undefined && key !== "id") {
        filteredData[key] = data[key];
      }
    }

    if (Object.keys(filteredData).length === 0) {
      throw new Error("修改数据字段为空");
    }

    const _user = ctx.state.user || {};
    filteredData.updateBy = _user.id;
    filteredData.updateDate = new Date();

    const sql = `UPDATE ${this.tableName} SET ? WHERE id = ?`;
    logDbQuery(sql, [filteredData, id]);
    const [result] = await pool.query(sql, [filteredData, id]);

    if (result.affectedRows === 1) {
      return { ...filteredData };
    } else {
      throw new Error("更新失败");
    }
  }

  /**
   * 删除记录
   * @param {number} id - 记录 ID
   * @returns {Promise<Object>} 被删除的记录对象
   * @throws {Error} 如果 id 无效
   */
  async remove(id, ctx) {
    if (!id) {
      throw new Error("主键ID 不能为空");
    }

    const sql = `DELETE FROM ${this.tableName} WHERE id = ?`;
    logDbQuery(sql, [id]);
    const [result] = await pool.query(sql, [id]);

    if (result.affectedRows === 1) {
      return true;
    } else {
      throw new Error("删除失败");
    }
  }

  /**
   * 软删除记录
   * @param {string} id - 删除的记录 ID
   * @returns {Promise<boolean>} 是否成功软删除
   * @throws {Error} 如果 id 无效
   */
  async softDelete(id, ctx) {
    if (!id) {
      throw new Error("主键ID 不能为空");
    }

    const sql = `UPDATE ${this.tableName} SET isDelete = 1 WHERE id = ?`;
    logDbQuery(sql, [id]);
    const [result] = await pool.query(sql, [id]);

    if (result.affectedRows === 1) {
      return true;
    } else {
      throw new Error("软删除失败");
    }
  }

  /**
   * 批量删除记录
   * @param {Array<string>} ids - 记录 ID 数组（UUID）
   * @returns {Promise<number>} 被删除的记录数量
   * @throws {Error} 如果 ids 无效
   */
  async removes(ids, ctx) {
    if (!Array.isArray(ids) || ids.length === 0) {
      throw new Error("批量删除时,主键ID 不能为空");
    }

    const placeholders = ids.map(() => "?").join(",");
    const sql = `DELETE FROM ${this.tableName} WHERE id IN (${placeholders})`;
    logDbQuery(sql, ids);
    const [result] = await pool.query(sql, ids);

    if (result.affectedRows > 0) {
      return result.affectedRows;
    } else {
      throw new Error("批量删除失败");
    }
  }

  /**
   * 批量软删除记录
   * @param {Array<string>} ids - 删除的记录 ID 列表（UUID）
   * @returns {Promise<number>} 被软删除的记录数量
   * @throws {Error} 如果 ids 无效
   */
  async softDeletes(ids, ctx) {
    if (!Array.isArray(ids) || ids.length === 0) {
      throw new Error("批量软删除时,主键ID 不能为空");
    }

    const placeholders = ids.map(() => "?").join(",");
    const sql = `UPDATE ${this.tableName} SET isDelete = 1 WHERE id IN (${placeholders})`;
    logDbQuery(sql, ids);
    const [result] = await pool.query(sql, ids);

    if (result.affectedRows > 0) {
      return result.affectedRows;
    } else {
      throw new Error("批量软删除失败");
    }
  }

  /**
   * 根据 ID 查询记录
   * @param {number} id - 记录 ID
   * @returns {Promise<Object|null>} 查询到的记录对象，如果不存在则返回 null
   * @throws {Error} 如果 id 无效
   */
  async getById(id, ctx) {
    if (!id) {
      throw new Error("主键ID 不能为空");
    }

    const sql = `SELECT * FROM ${this.tableName} WHERE id = ?`;
    logDbQuery(sql, [id]);
    const [rows] = await pool.query(sql, [id]);
    return rows.length > 0 ? rows[0] : null;
  }

  /**
   * 批量获取记录
   * @param {Array<string>} ids - 记录 ID 数组（UUID）
   * @returns {Promise<Array<Object>>} 记录对象数组
   * @throws {Error} 如果 ids 无效
   */
  async getByIds(ids, ctx) {
    if (!Array.isArray(ids) || ids.length === 0) {
      throw new Error("批量查询时,主键ID 不能为空");
    }

    const placeholders = ids.map(() => "?").join(",");
    const sql = `SELECT * FROM ${this.tableName} WHERE id IN (${placeholders})`;
    logDbQuery(sql, ids);
    const [rows] = await pool.query(sql, ids);
    return rows;
  }
}

module.exports = BaseModel;
